#!/usr/bin/env tclsh
# $Id: test3.tcl 9ce65bed8ef4 2012/08/26 02:14:51 pgurumur $
# Copyright (c) 2012 Prabhu Gurumurthy <pgurumur@gmail.com>
#
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notice and this permission notice appear in all copies.
#
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
# WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
# ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
# WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
# ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
#

# __version__  = "$Revision: 9ce65bed8ef4 $"
# __author__   = "$Author: pgurumur $"
# __modified__ = "$Date: 2012-08-26 02:14:51Z $"

lappend ::auto_path venya
package require sqlite3
package require mysqltcl 3.0
package require stdlib

namespace eval sql {
   namespace export sql
   namespace export mysql
   set version 1.0
   set dbh ""
   set type ""
}

proc sql::sqlite { database } {
   global dbh type

   if {[string length $database]} {
      sqlite3 $dbh $database
      set type "sqlite"
   }
}

proc sql::mysql { database host { username "" } { password "" } } {
   global tcl_platform dbh 
   global type

   set retval ""
   set user ""
   set pass ""
   set flag [string length $database]
   set flag [expr $flag && [string length $host]]

   if {[string length $username]} {
      set user $username
   } else {
      set user $tcl_platform(user)
   }

   if {[string length $password]} {
      set pass $password
   }

   if {[catch {set retval [mysql::connect -host $host -user $user -password $pass -db $database]} err]} {
      error $err
   } else {
      set dbh $retval
      set type "mysql"
   }
}

proc sql::close {} {
   global dbh type
   if {[string length $dbh]} {
      if {$type == "sqlite"} {
         $dbh close
      } else {
         mysql::close $dbh
      }

      set dbh ""
      set type ""
   }
}

proc sql::gettype {} {
   global type
   return $type
}

proc sql::execute { statement { select 0 } } {
   global dbh type
   set retval [list]
   if {$select == 1} {
      if {$type == "mysql"} {
         if {[mysql::ping $dbh]} {
            if {[ catch { set query [mysql::query $dbh $statement ] } err]} {
               error $err
            } else {
               while {[set row [::mysql::fetch $query]] != ""} {
                  lappend retval $row
               }
         
               mysql::endquery $query
            }
         }
      } elseif {$type == "sqlite"} {
         if {[catch {$dbh eval $statement} err]} {
            error $err
         } 
      }
   } else {
      if {$type == "mysql"} {
         if {[catch {mysql::exec $dbh $statement} err]} {
            error $err
         }
      } elseif {$type == "sqlite"} {
         if {[catch {$dbh eval $statement} err]} {
            error $err
         }
      }
   }

   return $retval
}

sql::mysql "netdot" "localhost" "djanet" "oS9XckUcTAHc5rjlcPt1"

set query "select device.monitored, device.id, concat(rr.name, '.', zone.name),"
set query [concat $query " product.name, inet_ntoa(ipblock.address), "]
set query [concat $query "sysdescription from device "]
set query [concat $query "left join product on product.id = device.product "]
set query [concat $query "left join ipblock on ipblock.id = device.snmp_target"]
set query [concat $query " left join rr on rr.id = device.name "]
set query [concat $query "left join zone on zone.id = rr.zone "]
# 
# set row [sql::execute "select id, name from site;" 1]
set row [sql::execute $query 1]
set values [list]
for {set ix 0} {$ix < [llength $row]} {incr ix} {
   set mrow [lindex $row $ix]
   lappend values [ concat  [lindex $mrow 2] [lindex $mrow 4]]
   # lappend values [lindex $mrow 2] [lindex $mrow 4]
}
# puts [llength $row]
# puts $row

sql::close

# sql::mysql "test" "localhost"
#id smallint unsigned not null auto_increment, hostname varchar(100) character set utf8, ipaddress decimal(41,0), primary key(id));
# for {set ix 0} {$ix < [llength $values]} {incr ix} {
   # set device [lindex [lindex $values $ix] 0]
   # set ip [stdlib::ip2pack [lindex [lindex $values $ix] 1]]
   # set query [format "insert into venya values (NULL, '%s', %d)" $device $ip]
   # puts $query
   # # sql::execute $query
# }
#

sql::sqlite "test.db"
set statement "create table if not exists venya ( id integer primary key, "
set statement [concat $statement "hostname unicode(100), "]
set statement [concat $statement "ipaddress decimal(41,0)); "]
# set statement [concat $statement "primary key(id))"]
sql::execute $statement
for {set ix 0} {$ix < [llength $values]} {incr ix} {
   set device [lindex [lindex $values $ix] 0]
   set ip [stdlib::ip2pack [lindex [lindex $values $ix] 1]]
   set query [format "insert into venya values (NULL, '%s', %d)" $device $ip]
   sql::execute $query
}

# puts [llength $values]
sql::close
